package milkproduction.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import milkproduction.to.ContainerTO;
import milkproduction.to.OrderTO;
import milkproduction.to.RowMilkTO;
import miracom.one.MilkProduction.util.DatabaseUtility;

public class GenerateDAO {
	 private static GenerateDAO instance =new GenerateDAO();
	 private GenerateDAO(){}
	 public static GenerateDAO getInstace(){
		 return instance;
	 }
//병 시퀀스
	 public  int selectBottleSEQ(){
			Connection conn =null;
			PreparedStatement pstmt=null;
			ResultSet rset=null;
			int seq = 0;
			DatabaseUtility dbu = DatabaseUtility.getInstance();
			try {
				conn = dbu.getDataSource().getConnection();
				String sql = "select CONTAINER_NO_SEQ.nextval from dual";
				pstmt=conn.prepareStatement(sql);
				rset = pstmt.executeQuery();
				if(rset.next()){
				 seq = rset.getInt(1);
				}}catch (Exception e) {
					e.printStackTrace();
			}finally{
					dbu.close(rset, pstmt, conn);
			}
			return seq ;
		}
	 
//원유 시퀀스
		 public  int selectRowMilkSEQ(){
				Connection conn =null;
				PreparedStatement pstmt=null;
				ResultSet rset=null;
				int seq = 0;
				DatabaseUtility dbu = DatabaseUtility.getInstance();
				try {
					conn = dbu.getDataSource().getConnection();
					String sql = "select ROW_MILK_NO_SEQ.nextval from dual";
					pstmt=conn.prepareStatement(sql);
					rset = pstmt.executeQuery();
					if(rset.next()){
					 seq = rset.getInt(1);
					}}catch (Exception e) {
						e.printStackTrace();
				}finally{
						dbu.close(rset, pstmt, conn);
				}
				return seq ;
			}
	 
//우유 주문자 시퀀스 
	 public  int selectOrderSEQ(){
			Connection conn =null;
			PreparedStatement pstmt=null;
			ResultSet rset=null;
			int seq = 0;
			DatabaseUtility dbu = DatabaseUtility.getInstance();
			try {
				conn = dbu.getDataSource().getConnection();
				String sql = "select ORDER_NO_SEQ.nextval from dual";
				pstmt=conn.prepareStatement(sql);
				rset = pstmt.executeQuery();
				if(rset.next()){
				 seq = rset.getInt(1);
				}}catch (Exception e) {
					e.printStackTrace();
			}finally{
					dbu.close(rset, pstmt, conn);
			}
			return seq ;
		}
	 
//우유 주문자 생성용
	 public  ArrayList selectByorderer(){
			Connection conn =null;
			PreparedStatement pstmt=null;
			ResultSet rset=null;
			ArrayList list = new ArrayList();
			DatabaseUtility dbu = DatabaseUtility.getInstance();
			try {
				conn = dbu.getDataSource().getConnection();
				String sql = "select orderer from orderer";
				pstmt=conn.prepareStatement(sql);
				rset = pstmt.executeQuery();
				while(rset.next()){
					list.add(rset.getString(1));
				}}catch (Exception e) {
					e.printStackTrace();
			}finally{
					dbu.close(rset, pstmt, conn);
			}
			return list ;
		}
	 
//우유 목장 생성용
	 public  ArrayList selectByMilk(){
			Connection conn =null;
			PreparedStatement pstmt=null;
			ResultSet rset=null;
			ArrayList list = new ArrayList();
			DatabaseUtility dbu = DatabaseUtility.getInstance();
			try {
				conn = dbu.getDataSource().getConnection();
				String sql = "select ranch from ranch";
				pstmt=conn.prepareStatement(sql);
				rset = pstmt.executeQuery();
				while(rset.next()){
					list.add(rset.getString(1));
				}}catch (Exception e) {
					e.printStackTrace();
			}finally{
					dbu.close(rset, pstmt, conn);
			}
			return list ;
		}
//병 메이커 생산용
	 public  ArrayList selectBottle(){
			Connection conn =null;
			PreparedStatement pstmt=null;
			ResultSet rset=null;
			ArrayList list = new ArrayList();
			DatabaseUtility dbu = DatabaseUtility.getInstance();
			try {
				conn = dbu.getDataSource().getConnection();
				String sql = "select maker from bottlMAKER";
				pstmt=conn.prepareStatement(sql);
				rset = pstmt.executeQuery();
				while(rset.next()){
					list.add(rset.getString(1));
				}}catch (Exception e) {
					e.printStackTrace();
			}finally{
					dbu.close(rset, pstmt, conn);
			}
			return list ;
		}
	 
//order 저장
	 public void insertOrder(OrderTO oto){
			Connection conn =null;
			PreparedStatement pstmt=null;
			DatabaseUtility dbu = DatabaseUtility.getInstance();
			int cnt = 0;
			try {
				conn=dbu.getDataSource().getConnection();
				
				String sql ="insert into ORDERS(ORDER_NO,ORDER_DATE,ORDERER,QUANTITY,DELIVERY_STATE) values(?,sysDate,?,?,'FALSE')";
				pstmt=conn.prepareStatement(sql);
				pstmt.setInt(1, oto.getOrderNo());
				pstmt.setString(2, oto.getOrderer());
				pstmt.setInt(3, oto.getQuantity());
				cnt = pstmt.executeUpdate();
			}		
				catch (SQLException e) {
					e.printStackTrace();
				}
				finally{
						dbu.close(pstmt, conn);
			}
		}

//원유 저장
	 public void insertRowMilk(RowMilkTO rmto){
			Connection conn =null;
			PreparedStatement pstmt=null;
			DatabaseUtility dbu = DatabaseUtility.getInstance();
			int cnt = 0;
			try {
				conn=dbu.getDataSource().getConnection();
				
				String sql ="insert into ROW_MILK(ROW_MILK_NO,STOCKED_DATE,CAPACITY,SUPPLIER) values(?,sysDate,?,?)";
				pstmt=conn.prepareStatement(sql);
				pstmt.setInt(1, rmto.getRowMilkNO());
				pstmt.setInt(2, (int) rmto.getCapacity());
				pstmt.setString(3, rmto.getSupplier());
				cnt = pstmt.executeUpdate();
			}		
				catch (SQLException e) {
					e.printStackTrace();
				}
				finally{
						dbu.close(pstmt, conn);
			}
		}
	 
//병 저장
	 public void insertBottle(ContainerTO cto){
			Connection conn =null;
			PreparedStatement pstmt=null;
			DatabaseUtility dbu = DatabaseUtility.getInstance();
			int cnt = 0;
			try {
				conn=dbu.getDataSource().getConnection();
				
				String sql ="insert into CONTAINER(CONTAINER_NO,STOCKED_DATE,QUANTITY,SUPPLIER) values(?,sysDate,?,?)";
				pstmt=conn.prepareStatement(sql);
				pstmt.setInt(1, cto.getContainerNo());
				pstmt.setInt(2, (int) cto.getQuantity());
				pstmt.setString(3, cto.getSupplier());
				cnt = pstmt.executeUpdate();
			}		
				catch (SQLException e) {
					e.printStackTrace();
				}
				finally{
						dbu.close(pstmt, conn);
			}
		}
//테스트용 메인
/*public static void main(String[] args) {

//	ArrayList i = getInstace().selectByMilk();
//	for (int j = 0; j < i.size(); j++) {
//		System.out.println(i.get(j));
//	}
		}*/
}//EOF

